<?php
/**
 * Created by PhpStorm
 * User: Jhinwy
 * Date: 3/19/24
 * Time: 6:52 PM
 */

namespace app\adminapi\lists\recycle;

use app\adminapi\lists\BaseAdminDataLists;
use app\adminapi\logic\auth\AdminLogic;
use app\common\lists\ListsSearchInterface;
use app\common\model\user\User;
use app\common\model\promotion\PromotionLink;
use app\common\model\recycle_report\RecycleReport;
use app\common\model\recycle_report\RecycleReportDetail;
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;


/**
 * 回收报表
 *
 * Class OrderLists
 * @package app\adminapi\lists
 */
class ReportLists extends BaseAdminDataLists implements ListsSearchInterface
{


    /**
     * 搜索条件
     *
     * @return array
     */
    public function setSearch(): array
    {
        return [];
    }


    /**
     * 回收列表
     *
     * @return array
     */
    public function lists(): array
    {
        // 是否根据推广链分组
        $isPromotionLink = $this->request->get('is_promotion_link', false);

        $group = 'count_date';
        $field = 'count_date,
                sum(cost_money) as total_cost_money,
                sum(add_up_recharge_money) as total_add_up_recharge_money, 
                sum(add_up_fans_pay_num) as total_add_up_fans_pay_num,
                sum(new_fans_recharge_money) as total_new_fans_recharge_money,
                sum(new_fans_num) as total_new_fans_num,
                sum(add_new_fans_num) as total_add_new_fans_num,
                sum(day_login_num) as total_day_login_num,
                sum(new_fans_pay_num) as total_new_fans_pay_num, 
                sum(shield_fans_num) as total_shield_fans_num,
                sum(add_desktop_num) as total_add_desktop_num, 
                sum(add_up_order_num) as total_add_up_order_num,
                sum(day_order_num) as total_day_order_num,
                sum(annual_fee_recharge_money) as total_annual_fee_recharge_money';

        if (filter_var($isPromotionLink, FILTER_VALIDATE_BOOLEAN)) {
            $group .= ',promotion_link_id';
            $field = 'promotion_link_id,' . $field;
        }


        $list = RecycleReport::field($field)
            ->where($this->queryWhere())
            ->group($group)
            ->order('count_date', 'desc')
            ->limit($this->limitOffset, $this->limitLength)
            ->select();
        foreach ($list as &$item) {
            $count_date                      = strtotime($item->count_date);
            $date                            = strtotime(date('Y-m-d', time()));
            $seconds_diff                    = $date - $count_date;
            $item->day                       = abs($seconds_diff / (60 * 60 * 24)) + 1;
            $item->cost_money                = $item->total_cost_money;
            $item->add_up_recharge_money     = $item->total_add_up_recharge_money;
            $item->add_up_fans_pay_num       = $item->total_add_up_fans_pay_num;
            $item->new_fans_recharge_money   = $item->total_new_fans_recharge_money;
            $item->new_fans_num              = $item->total_new_fans_num;
            $item->add_new_fans_num          = $item->total_add_new_fans_num;
            $item->day_login_num             = $item->total_day_login_num;
            $item->new_fans_pay_num          = $item->total_new_fans_pay_num;
            $item->shield_fans_num           = $item->total_shield_fans_num;
            $item->add_desktop_num           = $item->total_add_desktop_num;
            $item->add_up_order_num          = $item->total_add_up_order_num;
            $item->day_order_num             = $item->total_day_order_num;
            $item->annual_fee_recharge_money = $item->total_annual_fee_recharge_money;

            $item->add_up_recycle_rate = getRate($item->add_up_recharge_money, $item->cost_money); // 累计回收率
            $item->roi                 = getRate($item->new_fans_recharge_money, $item->cost_money);  // 首日回收roi
            $item->new_fans_price      = getDiv($item->cost_money, $item->new_fans_num);  // 粉丝单价
            $item->add_desktop_cost    = getDiv($item->cost_money, $item->add_desktop_num);    // 加桌成本
            $item->add_desktop_rate    = getRate($item->add_desktop_num, $item->new_fans_num); // 加桌率
            $item->new_fans_pay_price  = getDiv($item->cost_momey, $item->new_fans_pay_num); // 付费新粉单价
            $item->order_price         = getDiv($item->new_fans_recharge_money, $item->day_order_num);  // 订单单价
            $item->new_fans_pay_rate   = getRate($item->new_fans_pay_num, $item->add_desktop_num); // 新粉付费率
            $item->new_fans_arpu       = getDiv($item->new_fans_recharge_money, $item->new_fans_pay_num);  // 新粉ARPU
            $linkIds                   = RecycleReport::where('count_date', $item->count_date)->column('promotion_link_id');
            //            $count_date_start = strtotime($item->count_date);
            //            $count_date_end = strtotime($item->count_date.' 23:59:59');
            //            $day_new_user                    = User::where('promotion_link_id','in',$linkIds)->whereBetween('login_time',[$count_date_start,$count_date_end])->count();
            $item->total_fans_num = $this->getTotalFans(array_unique($linkIds));   // 总粉丝数
            //            $item->new_fun_rate              = getRate($item->new_fans_num, $item->total_fans_num); // 新粉占比   今日推广链接进来的新用户粉丝数除以今日推广链进来的总粉丝数        (新增粉丝 / 总粉丝数)旧
            //            $item->new_fun_rate              = getRate($item->add_new_fans_num, $item->new_fans_num); // 新粉占比   今日推广链接进来的新用户粉丝数除以今日推广链进来的总粉丝数        (新增粉丝 / 总粉丝数)旧
            $item->new_fun_rate              = getRate($item->new_fans_num, $item->day_login_num); // 新粉占比   今日推广链接进来的新用户粉丝数除以该推广链快应用今日总调起数 再*百分之百        (新增粉丝 / 总粉丝数)旧
            $item->shield_fans_rate          = getRate($item->shield_fans_num, $item->new_fans_num); // 屏蔽占比
            $item->add_up_new_fans_pay_price = getDiv($item->cost_money, $item->add_up_fans_pay_num); // 累计付费粉单价
            $item->add_up_order_price        = getDiv($item->add_up_recharge_money, $item->add_up_order_num);  // 累计订单单价
            $item->annual_fee_rate           = getRate($item->annual_fee_recharge_num, $item->new_fans_pay_num); // 年费充值占比
            // 回收
            $recycleReportDetailList = RecycleReportDetail::where($this->queryWhere())->where('count_date', $item->count_date)->field('day,sum(recharge_money) as recharge_money')->group('day')->select();
            $detailList              = $this->formatRecycleReportDetailList($recycleReportDetailList, $item->cost_money);
            $item->data              = $detailList;
        }

        return $list->toArray();
    }

    public function formatRecycleReportDetailList($recycleReportDetailList, $cost_money)
    {

        $total_recharge_money = 0;
        $last_recycle_rate    = null;
        foreach ($recycleReportDetailList as &$item) {
            /*
             * 当日充值：detail表查出来的 recharge_money
             * 回收率：D1+Dn总充值除总成本。没填成本无法计算
             * 增长率：Dn-（Dn-1） 当日回收率减昨日回收率
             * 倍率：  Dn累计充值金额 / D1 充值金额  如果第一天无充值不计算
             * */
            $first_recharge_money = 0;
            $item['increase']     = $item['recharge_money'];
            $total_recharge_money += $item['recharge_money'];
            if ($cost_money > 0) {
                $item['recycle_rate'] = bcmul(bcdiv($total_recharge_money, $cost_money, 4), 100, 2);
            } else {
                $item['recycle_rate'] = null;
            }
            if ($item['day'] == 1) {
                $first_recharge_money = $item['recharge_money'];
            }

            if (!empty($item['recycle_rate'])) {
                if ($item['day'] == 1) {
                    $item['increase_rate'] = null;
                } else {
                    $item['increase_rate'] = bcsub($item['recycle_rate'], $last_recycle_rate, 2);
                }
                $last_recycle_rate = $item['recycle_rate'];
            } else {
                $item['recycle_rate'] = null;
            }
            if ($first_recharge_money != 0) {
                $item['magnification'] = bcdiv($total_recharge_money, $first_recharge_money, 2);
            } else {
                $item['magnification'] = null;
            }
        }
        $array       = $recycleReportDetailList->toArray();
        $days        = array_column($array, 'day');
        $missingDays = array_diff(range(1, 60), $days);
        foreach ($missingDays as $day) {
            $array[] = [
                "day"            => $day,
                "recharge_money" => null,
                "recycle_rate"   => null,
                "increase"       => null,
                "increase_rate"  => null,
                "magnification"  => null,
            ];
        }
        usort($array, function ($a, $b) {
            return $a['day'] - $b['day'];
        });

        foreach ($array as &$item) {
            if (empty($item['recharge_money'])) {
                $item['recharge_money'] = '-';
            } else {
                $item['recharge_money'] = formatDecimal($item['recharge_money']) . '元';
            }
            if (empty($item['recycle_rate'])) {
                $item['recycle_rate'] = '-';
            } else {
                $item['recycle_rate'] = formatDecimal($item['recycle_rate']) . '%';
            }
            if (empty($item['increase'])) {
                $item['increase'] = '-';
            } else {
                $item['increase'] = formatDecimal($item['increase']);
            }
            if (empty($item['increase_rate'])) {
                $item['increase_rate'] = '-';
            } else {
                $item['increase_rate'] = formatDecimal($item['increase_rate']) . '%';
            }
            if (empty($item['magnification'])) {
                $item['magnification'] = '-';
            } else {
                $item['magnification'] = formatDecimal($item['magnification']) . '倍';
            }
        }
        return $array;
    }

    /**
     * 回收汇总
     *
     * @return array
     */
    public function getTotal()
    {


        $recycleReport = RecycleReport::where($this->queryWhere())
            ->field('
                          sum(new_fans_recharge_money) as new_fans_recharge_money,
                          sum(cost_money) as cost_money,
                          sum(add_up_recharge_money ) as add_up_recharge_money,
                          sum(add_up_fans_pay_num) as add_up_fans_pay_num,
                          sum(new_fans_num) as new_fans_num,
                          sum(add_new_fans_num) as add_new_fans_num,
                          sum(new_fans_pay_num) as new_fans_pay_num,
                          sum(day_login_num) as day_login_num,
                          sum(shield_fans_num) as shield_fans_num,
                          sum(add_desktop_num) as add_desktop_num,
                          sum(add_up_order_num) as add_up_order_num,
                          sum(day_order_num) as day_order_num,
                          sum(annual_fee_recharge_money) as annual_fee_recharge_money,
                          sum(annual_fee_recharge_num) as annual_fee_recharge_num'
            )
            ->findOrEmpty();
        $whereArr      = $this->queryWhere();
        $reportWhere   = [];
        foreach ($whereArr as $item) {
            if (in_array('promotion_link_id', $item)) {
                $item[array_search('promotion_link_id', $item)] = 'id';
                $reportWhere[]                                  = $item;
                break; // 如果只需要替换第一个匹配项，可以加上 break 来提高效率
            }
        }
        //        $linkIds                   = RecycleReport::where($reportWhere)->column('promotion_link_id');
        $total_fans_num = PromotionLink::where($reportWhere)->sum('add_fans');
        if (!$recycleReport->isEmpty()) {
            $recycleReport->total_fans_num      = $total_fans_num;
            $recycleReport->add_up_recycle_rate = getRate($recycleReport->add_up_recharge_money, $recycleReport->cost_money);// 累计回收率
            $recycleReport->new_fans_price      = getDiv($recycleReport->cost_money, $recycleReport->new_fans_num);// 粉丝单价
            $recycleReport->add_desktop_cost    = getDiv($recycleReport->cost_money, $recycleReport->add_desktop_num);// 加桌成本
            $recycleReport->add_desktop_rate    = getRate($recycleReport->add_desktop_num, $recycleReport->new_fans_num);// 加桌率
            $recycleReport->new_fans_pay_price  = getDiv($recycleReport->cost_money, $recycleReport->new_fans_pay_num);// 付费新粉单价
            $recycleReport->order_price         = getDiv($recycleReport->new_fans_recharge_money, $recycleReport->day_order_num);// 订单单价
            $recycleReport->new_fans_pay_rate   = getRate($recycleReport->new_fans_pay_num, $recycleReport->add_desktop_num);// 新粉付费率,
            $recycleReport->new_fans_arpu       = getDiv($recycleReport->new_fans_recharge_money, $recycleReport->new_fans_pay_num);// 新粉ARPU
            //            $recycleReport->new_fun_rate = getRate($recycleReport->new_fans_num, $total_fans_num);// 新粉占比 (新增粉丝 / 总粉丝数)
            //            $recycleReport->new_fun_rate = getRate($recycleReport->add_new_fans_num, $recycleReport->new_fans_num);// 新粉占比 (新增粉丝 / 总粉丝数)
            $recycleReport->new_fun_rate              = getRate($recycleReport->new_fans_num, $recycleReport->day_login_num);// 新粉占比 (新增粉丝 / 总粉丝数)
            $recycleReport->shield_fans_rate          = getRate($recycleReport->shield_fans_num, $recycleReport->new_fans_num);// 屏蔽占比
            $recycleReport->add_up_new_fans_pay_price = getDiv($recycleReport->cost_money, $recycleReport->add_up_fans_pay_num);// 累计付费粉单价
            $recycleReport->add_up_order_price        = getDiv($recycleReport->add_up_recharge_money, $recycleReport->add_up_order_num);// 累计订单单价
            $recycleReport->annual_fee_rate           = getRate($recycleReport->annual_fee_recharge_num, $recycleReport->new_fans_pay_num);// 年费充值占比
        }
        return $recycleReport->toArray();

    }


    /**
     * 获取总粉丝数
     *
     * @param $linkId
     * @return mixed
     */
    public function getTotalFans($linkIds)
    {
        return PromotionLink::whereIn('id', $linkIds)->sum('add_fans');
    }


    public function getRecycle($list, $date, $limit = 7)
    {

        $data = $list->where('count_date', '>', $date)->order('count_date', 'asc')->toArray();
        $data = array_slice($data, 0, $limit);

        // 按日期排序
        usort($data, function ($a, $b) {
            return strtotime($a['count_date']) - strtotime($b['count_date']);
        });
        $result = [];
        for ($i = 0; $i < $limit; $i++) {
            if ($data[$i] ?? []) {
                // dd($data[$i]['count_date']);
                $yesData = $list->where('count_date', '<', $data[$i]['count_date'])->first();
                if (!$yesData) {
                    $yesData = [];
                } else {
                    $yesData = $yesData->toArray();
                }

                $result[$i] = [
                    'recharge_money' => $data[$i]['new_fans_recharge_money'] ?? '-',
                    'recycle_rate'   => getRate($data[$i]['add_up_recharge_money'] ?? 0, $data[$i]['cost_money'] ?? 0),
                    'increase'       => $this->getIncrease($yesData, $data, $i),
                    'increase_rate'  => $this->getIncreaseRate($data[$i] ?? [], $yesData ?? []),
                    'magnification'  => $this->getMagnification($data[$i] ?? [], $yesData ?? [])
                ];
            } else {
                $result[$i] = [
                    'recharge_money' => '-',
                    'recycle_rate'   => '-',
                    'increase'       => '-',
                    'increase_rate'  => '-',
                    'magnification'  => '-',
                ];
            }
        }

        return $result;
    }


    /**
     * 增幅 首日至今总回收-首日至昨日总回收
     *
     * @param $today
     * @param $yesterday
     * @return float|string
     */
    public function getIncrease($yesterday, $data, $i)
    {
        $today = array_slice($data, $i, 1);
        if (!$today || !$yesterday) {

            return '-';
        }
        $today = array_merge($today, $yesterday);

        $today     = array_sum(array_column($today, 'add_up_recharge_money')) ?? 0;
        $yesterday = array_sum(array_column($yesterday, 'add_up_recharge_money')) ?? 0;

        return round($today - $yesterday, 2);
    }


    /**
     * 增幅率
     *
     * @param $today
     * @param $yesterday
     * @return string
     */
    public function getIncreaseRate($today, $yesterday)
    {
        if (!$today || !$yesterday) {

            return '-';
        }

        $todayMoney     = $this->getRecycleRate($today['add_up_recharge_money'] ?? 0, $today['cost_money'] ?? 0);
        $yesterdayMoney = $this->getRecycleRate($yesterday['add_up_recharge_money'] ?? 0, $yesterday['cost_money'] ?? 0);
        if (!is_numeric($todayMoney) || !!is_numeric($yesterdayMoney)) {

            return '-';
        }

        return round(bcmul($todayMoney - $yesterdayMoney, 100, 2)) . '%';
    }


    /**
     * 获取倍率
     *
     * @param $today
     * @param $yesterday
     * @return float
     */
    public function getMagnification($today, $yesterday)
    {
        if (!$today || !$yesterday) {

            return '-';
        }

        $todayMoney     = $this->getRecycleRate($today['add_up_recharge_money'] ?? 0, $today['cost_money'] ?? 0);
        $yesterdayMoney = $this->getRecycleRate($yesterday['add_up_recharge_money'] ?? 0, $yesterday['cost_money'] ?? 0);
        if (!is_numeric($todayMoney) || !!is_numeric($yesterdayMoney)) {

            return '-';
        }

        return round(bcmul($todayMoney / $yesterdayMoney, 100, 2)) . '%';
    }


    /**
     * 获取回收率
     *
     * @param $add_up_recharge_money
     * @param $cost_money
     * @return int|string
     */
    public function getRecycleRate($add_up_recharge_money, $cost_money)
    {
        if (!$add_up_recharge_money || !$cost_money || $add_up_recharge_money == 0.00 || $cost_money == 0.00) {

            return '-';
        }

        return ($add_up_recharge_money == 0 || $cost_money == 0) ? '-' : bcdiv($add_up_recharge_money, $cost_money, 4);
    }


    /**
     * 分页计数
     *
     * @return int
     */
    public function count(): int
    {
        $isPromotionLink = $this->request->get('is_promotion_link', false);
        $group           = 'count_date';
        if (filter_var($isPromotionLink, FILTER_VALIDATE_BOOLEAN)) {
            $group .= ',promotion_link_id';
        }
        return RecycleReport::where($this->queryWhere())->group($group)->select()->count();
    }


    /**
     * 搜索条件
     *
     * @return RecycleReport
     */
    public function queryWhere()
    {
        $params          = $this->request->get();
        $mediaId         = $params['media_id'] ?? '';
        $status          = $params['status'] ?? '';
        $platformId      = $params['platform_id'] ?? '';
        $adminId         = $params['admin_id'] ?? '';
        $startDate       = $params['start_date'] ?? '';
        $endDate         = $params['end_date'] ?? '';
        $promotionLinkId = $params['promotion_link_id'] ?? '';
        $book            = $params['book'] ?? '';
        $pAdminIds       = [];

        // TODO 管理员权限控制
        if ($this->adminInfo['root'] != 1) {
            $pAdminIds = AdminLogic::getPermissionAdminIds($this->adminInfo);
        }
        $promotion_where = [];
        if ($promotionLinkId) {
            $promotion_where[] = ['id', '=', $promotionLinkId];
        }
        if ($adminId) {
            $promotion_where[] = ['admin_id', '=', $adminId];
        }
        if ($pAdminIds) {
            $promotion_where[] = ['admin_id', 'in', $pAdminIds];
        }
        if ($mediaId) {
            $promotion_where[] = ['media', '=', $mediaId];
        }
        if ($status !== '') {
            $promotion_where[] = ['status', '=', $status];
        }
        if ($platformId) {
            $promotion_where[] = ['platform', '=', $platformId];
        }
        if ($book) {
            if (is_int($book)) {
                $promotion_where[] = ['book_id', '=', $book];
            } else {
                $promotion_where[] = ['book_name', 'like', '%' . $book . '%'];
            }
        }
        $where              = [];
        $promotionLinkIDArr = PromotionLink::where($promotion_where)->column('id');
        if ($startDate && $endDate) {
            $where[] = ['count_date', 'between', [$startDate, $endDate]];
        }
        $where[] = ['promotion_link_id', 'in', $promotionLinkIDArr];
        return $where;
    }


    /**
     * 导出
     *
     * @return string
     */
    public function export()
    {
        $list = RecycleReport::
        field('
                count_date,
                sum(cost_money) as total_cost_money,
                sum(add_up_recharge_money) as total_add_up_recharge_money, 
                sum(add_up_fans_pay_num) as total_add_up_fans_pay_num,
                sum(new_fans_recharge_money) as total_new_fans_recharge_money,
                sum(new_fans_num) as total_new_fans_num,
                sum(add_new_fans_num) as total_add_new_fans_num,
                sum(day_login_num) as total_day_login_num,
                sum(new_fans_pay_num) as total_new_fans_pay_num, 
                sum(shield_fans_num) as total_shield_fans_num,
                sum(add_desktop_num) as total_add_desktop_num, 
                sum(add_up_order_num) as total_add_up_order_num,
                sum(day_order_num) as total_day_order_num,
                sum(annual_fee_recharge_money) as total_annual_fee_recharge_money
            ')->where($this->queryWhere())
            ->group('count_date')
            ->order('count_date', 'desc')
            ->select();
        foreach ($list as &$item) {
            $count_date                      = strtotime($item->count_date);
            $date                            = strtotime(date('Y-m-d', time()));
            $seconds_diff                    = $date - $count_date;
            $item->day                       = abs($seconds_diff / (60 * 60 * 24)) + 1;
            $item->cost_money                = $item->total_cost_money;
            $item->add_up_recharge_money     = $item->total_add_up_recharge_money;
            $item->add_up_fans_pay_num       = $item->total_add_up_fans_pay_num;
            $item->new_fans_recharge_money   = $item->total_new_fans_recharge_money;
            $item->new_fans_num              = $item->total_new_fans_num;
            $item->add_new_fans_num          = $item->total_add_new_fans_num;
            $item->day_login_num             = $item->total_day_login_num;
            $item->new_fans_pay_num          = $item->total_new_fans_pay_num;
            $item->shield_fans_num           = $item->total_shield_fans_num;
            $item->add_desktop_num           = $item->total_add_desktop_num;
            $item->add_up_order_num          = $item->total_add_up_order_num;
            $item->day_order_num             = $item->total_day_order_num;
            $item->annual_fee_recharge_money = $item->total_annual_fee_recharge_money;

            $item->add_up_recycle_rate = getRate($item->add_up_recharge_money, $item->cost_money); // 累计回收率
            $item->roi                 = getRate($item->new_fans_recharge_money, $item->cost_money);  // 首日回收roi
            $item->new_fans_price      = getDiv($item->cost_money, $item->new_fans_num);  // 粉丝单价
            $item->add_desktop_cost    = getDiv($item->cost_money, $item->add_desktop_num);    // 加桌成本
            $item->add_desktop_rate    = getRate($item->add_desktop_num, $item->new_fans_num); // 加桌率
            $item->new_fans_pay_price  = getDiv($item->cost_momey, $item->new_fans_pay_num); // 付费新粉单价
            $item->order_price         = getDiv($item->new_fans_recharge_money, $item->day_order_num);  // 订单单价
            $item->new_fans_pay_rate   = getRate($item->new_fans_pay_num, $item->add_desktop_num); // 新粉付费率
            $item->new_fans_arpu       = getDiv($item->new_fans_recharge_money, $item->new_fans_pay_num);  // 新粉ARPU
            $linkIds                   = RecycleReport::where('count_date', $item->count_date)->column('promotion_link_id');
            //            $count_date_start = strtotime($item->count_date);
            //            $count_date_end = strtotime($item->count_date.' 23:59:59');
            //            $day_new_user                    = User::where('promotion_link_id','in',$linkIds)->whereBetween('login_time',[$count_date_start,$count_date_end])->count();
            $item->total_fans_num = $this->getTotalFans(array_unique($linkIds));   // 总粉丝数
            //            $item->new_fun_rate              = getRate($item->new_fans_num, $item->total_fans_num); // 新粉占比   今日推广链接进来的新用户粉丝数除以今日推广链进来的总粉丝数        (新增粉丝 / 总粉丝数)旧
            //            $item->new_fun_rate              = getRate($item->add_new_fans_num, $item->new_fans_num); // 新粉占比   今日推广链接进来的新用户粉丝数除以今日推广链进来的总粉丝数        (新增粉丝 / 总粉丝数)旧
            $item->new_fun_rate   = getRate($item->new_fans_num, $item->day_login_num); // 新粉占比   今日推广链接进来的新用户粉丝数除以今日推广链进来的总粉丝数        (新增粉丝 / 总粉丝数)旧
            $item->total_fans_num = $this->getTotalFans(array_unique($linkIds));   // 总粉丝数
            //            $item->new_fun_rate              = getRate($item->new_fans_num, $item->total_fans_num); // 新粉占比 (新增粉丝 / 总粉丝数)
            $item->shield_fans_rate          = getRate($item->shield_fans_num, $item->new_fans_num); // 屏蔽占比
            $item->add_up_new_fans_pay_price = getDiv($item->cost_money, $item->add_up_fans_pay_num); // 累计付费粉单价
            $item->add_up_order_price        = getDiv($item->add_up_recharge_money, $item->add_up_order_num);  // 累计订单单价
            $item->annual_fee_rate           = getRate($item->annual_fee_recharge_num, $item->new_fans_pay_num); // 年费充值占比
            // 回收
            //            $recycleReportDetailList = RecycleReportDetail::where($this->queryWhere())->where('count_date',$item->count_date)->field('day,sum(recharge_money) as recharge_money')->group('day')->select();
            //            $detailList = $this->formatRecycleReportDetailList($recycleReportDetailList,$item->cost_money);
            //            $item->data = $detailList;
        }

        $total = $this->getTotal();
        $data  = $list->toArray();

        return $this->excel($total, $data);
    }


    /**
     * 导出excel
     *
     * @param $total
     * @param $list
     * @return array
     */
    private function excel($total, $list)
    {
        // 获取Spreadsheet对象
        $spreadsheet = new Spreadsheet();

        // 创建汇总sheet
        $sheet1 = $spreadsheet->getActiveSheet();
        $sheet1->setTitle('汇总');
        // 表头单元格内容 第一行
        $tit1Col = 'A';
        foreach ($this->getExportTitle() as $value) {
            // 单元格内容写入
            $sheet1->setCellValue($tit1Col . '1', $value);
            $tit1Col++;
        }
        // 从第二行开始写入数据
        $row1     = 2;
        $data1Col = 'A';
        foreach ($total as $item) {
            // 单元格内容写入
            $sheet1->setCellValue($data1Col . $row1, $item);
            $data1Col++;
        }

        // 创建明细sheet
        $spreadsheet->createSheet();
        $sheet2 = $spreadsheet->getSheet(1);
        $sheet2->setTitle('明细');
        // 表头单元格内容 第一行
        $tit2Col = 'A';
        foreach ($this->getExportTitle('明细') as $value) {
            // 单元格内容写入
            $sheet2->setCellValue($tit2Col . '1', $value);
            $tit2Col++;
        }
        // 从第二行开始写入数据
        $row2 = 2;
        foreach ($list as $item) {
            $data2Col = 'A';
            foreach ($item as $value) {
                // 单元格内容写入
                $sheet2->setCellValue($data2Col . $row2, $value);
                $data2Col++;
            }
            $row2++;
        }

        $writer   = new Xlsx($spreadsheet);
        $filename = 'excel/' . date('Y-m-d') . '回收报表.xlsx';
        $writer->save($filename);
        chmod(public_path('excel') . date('Y-m-d') . '回收报表.xlsx', 0777);

        return [public_path('excel') . date('Y-m-d') . '回收报表.xlsx', date('Y-m-d') . '回收报表.xlsx'];
    }


    /**
     * 获取导出标题字段
     *
     * @param string $type
     * @return array
     */
    protected function getExportTitle($type = '汇总')
    {
        if ($type == '汇总') {
            return [
                '新粉充值', '总成本', '累计充值','累计付费粉丝数', '新增粉丝数', '付费新粉数', '屏蔽粉丝数',
                '加桌人数', '累计订单数', '当日订单数', '首日年费充值', '首日年费充值人数', '总粉丝数', '累计回收率', '粉丝单价', '加桌成本', '加桌率', '付费新粉单价',
                '订单单价', '新粉付费率', '新粉ARPU', '新粉占比', '屏蔽占比', '累计付费粉单价', '累计订单单价', '首日年费占比'
            ];
        } else {
            return [
                '日期', '总成本', '累计充值', '累计付费粉丝数', '新粉充值', '新增粉丝数', '付费新粉数', '屏蔽粉丝数', '加桌人数',
                '累计订单数', '首日订单数', '首日年费充值', '累计回收率', '首日roi', '粉丝单价', '加桌成本', '加桌率', '付费新粉单价',
                '订单单价', '新粉付费率', '新粉ARPU', '总粉丝数', '新粉占比', '屏蔽占比', '累计付费粉单价', '累计订单单价', '首日年费占比'
            ];
        }
    }
}
